# -*- coding: utf-8 -*-
"""
Spyder 编辑器

这是一个临时脚本文件。
"""

from sqlalchemy import create_engine
import pymysql
conn=create_engine('mysql+pymysql://root:123456@localhost:3306/lings?charset=utf8')
import pandas as pd
file=pd.read_sql("select*from tbl_course",conn)
df1=pd.DataFrame(file)
df2=pd.read_csv('C:/Users/PC/Desktop/lecturer.csv')
df2.rename(columns={'id':'lecturer_id'},inplace=True)
df3=pd.merge(df1,df2,how="inner",on="lecturer_id")
df3['class_hours']=df3['class_hours'].map(lambda x:x.rstrip(','))
df3['class_hours']=df3['class_hours'].astype(int)
df3['class_week']=df3.apply(lambda row:(row['class_hours']/row['hours_per_week']),axis=1)
del df3['dependency']
del df3['lecturer_id']
del df3['open_time']
del df3['create_time']
del df3['credit']
del df3['class_hours']
del df3['hours_per_week']
del df3['last_update_time']
del df3['级别']
df4=df3[df3['工龄']<=9]
del df4['工龄']
df4=df4.sort_values(by="id" , ascending=True) 
df4.to_sql("1apython",conn,index=False,index_label="id")